/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package reportcenter.support.dbutil;

import reportcenter.support.resourceload.DataSourceConfig;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author renan_luz
 */
public class TestConnection {
    
    public void conectaOracle(){
        Connection conn = null;
        Statement stmt = null;
        new ConnectionManager(new DataSourceConfig("oracleqqserver1"));

        try {

            conn = ConnectionManager.ds.getConnection();

            ConnectionManager.printDriverStats();

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(TestConnection.sql);
            while (rs.next()) {
                System.out.println("Carinha -> " + rs.getString(4));
            }



        } catch (Exception e) {
            e.printStackTrace();
//                LOG.debug("Failed to execute a JDBC task: ", e );
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
//                    LOG.error ( "Failed to finalize JDBC task: ", ex );
                }
        }
    }
    
    public void conectaJavaDb(){
        Connection conn = null;
        Statement stmt = null;
        new ConnectionManager(new DataSourceConfig("javadb"));

        try {

            conn = ConnectionManager.ds.getConnection();

            ConnectionManager.printDriverStats();

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from data_source");
            while (rs.next()) {
                System.out.println("Carinha -> " + rs.getString(4));
            }



        } catch (Exception e) {
            e.printStackTrace();
//                LOG.debug("Failed to execute a JDBC task: ", e );
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
//                    LOG.error ( "Failed to finalize JDBC task: ", ex );
                }
        }
    }

    public static void main(String[] args) {

        TestConnection test = new TestConnection();
        test.conectaOracle();
        test.conectaJavaDb();
        test.conectaJavaDb();

    }
    
    static String sql = "select operacoes.data_operacao, operacoes.data_repasse, " +
            "     operacoes.codigo_loja, " +
            "     operacoes.tipo_operacao, " +
            "     operacoes.nome_cliente, " +
            "     operacoes.contrato, " +
            "     operacoes.numero_cartao, " +
            "     operacoes.nr_parcela, " +
            "     operacoes.valor_bruto, " +
            "     (select nvl(sum(vr_rep), 0) " +
            "        from verdecard.t416lafi " +
            "       where nr_ctr = operacoes.contrato " +
            "         and sg_fuc_hst = 'REPCMSEC') comissao, " +
            "     operacoes.valor_liquido " +
            "from (select trunc(min(oper.dt_lan)) data_operacao, " +
            "            min(oper.dt_rep) data_repasse, " +
            "            oper.cd_loj codigo_loja, " +
            "            oper.nr_ctr contrato, " +
            "            oper.cd_car numero_cartao, " +
            "            oper.nm_por nome_cliente, " +
            "            max(ds_prd) tipo_operacao, " +
            "            (select sum(abs(vr_rep)) " +
            "               from verdecard.t416lafi " +
            "              where t416lafi.nr_ctr = oper.nr_ctr " +
            "                and nvl(t416lafi.ad_lib, 0) = nvl(oper.ad_lib, 0) " +
            "                and sg_fuc_hst not in " +
            "                    ('REPVRTEFD', 'REPVRTEF', 'REPVRPOS', 'REPVRURA', " +
            "                     'REPVRWEB', 'REPVRADM', 'REPVRTUC')) valor_bruto, " +
            "            min(oper.nr_par) nr_parcela, " +
            "            (select max(nr_par) " +
            "               from verdecard.t416lafi " +
            "              where t416lafi.nr_ctr = oper.nr_ctr " +
            "                and nvl(t416lafi.ad_lib, 0) = nvl(oper.ad_lib, 0)) qt_parcelas, " +
            "            (sum(oper.vr_par)) valor_parcelas, " +
            "            sum(oper.vr_adm) valor_administrador, " +
            "            sum(oper.vr_tuc) valor_tuc, " +
            "            sum(oper.vr_meio) valor_meio_captura, " +
            "            (sum(oper.vr_par) + " +
            "            (sum(oper.vr_adm) + sum(oper.vr_tuc) + sum(oper.vr_meio))) valor_liquido " +
            "       from (select lafi.nr_ctr, " +
            "                    lafi.ad_lib, " +
            "                    lafi.nr_par, " +
            "                    lafi.dt_lan, " +
            "                    lafi.dt_rep, " +
            "                    lafi.cd_loj, " +
            "                    cart.cd_car, " +
            "                    cart.nm_por, " +
            "                    (case " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCC' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CC') " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCD' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CD') " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCI' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CI') " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCL' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CL') " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCJ' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CJ') " +
            "                      when lafi.sg_fuc_hst = 'REPVRCOMCS' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'CS') " +
            "                      when lafi.sg_fuc_hst = 'COMPREPAGO' then " +
            "                       (select ds_prd from autorizador.t411prod where sg_prd = 'PP') " +
            "                      else " +
            "                       '' " +
            "                    end) ds_prd, " +
            "                    nvl((case " +
            "                          when lafi.sg_fuc_hst in " +
            "                               ('REPVRCOMCC', 'REPVRCOMCD', 'REPVRCOMCI', " +
            "                                'REPVRCOMCL', 'REPVRCOMCJ', 'REPVRCOMCS', " +
            "                                'COMPREPAGO') then " +
            "                           vr_rep " +
            "                        end), " +
            "                        0) vr_par, " +
            "                    nvl((case " +
            "                          when lafi.sg_fuc_hst in ('REPVRTUC') then " +
            "                           vr_rep " +
            "                        end), " +
            "                        0) vr_tuc, " +
            "                    nvl((case " +
            "                          when lafi.sg_fuc_hst in ('REPVRADM') then " +
            "                           vr_rep " +
            "                        end), " +
            "                        0) vr_adm, " +
            "                    nvl((case " +
            "                          when lafi.sg_fuc_hst in " +
            "                               ('REPVRTEFD', 'REPVRTEF', 'REPVRPOS', 'REPVRURA', " +
            "                                'REPVRWEB') then " +
            "                           vr_rep " +
            "                        end), " +
            "                        0) vr_meio " +
            "               from verdecard.t416lafi lafi " +
            "               left outer join verdecard.t416copa copa on (copa.nr_ctr = lafi.nr_ctr) " +
            "               left outer join autorizador.t411cart cart on (cart.cd_car = copa.cd_car) " +
            "              where " +
            "                    trunc(lafi.dt_rep) = to_date('09/09/2008', 'DD/MM/YYYY') " +
            //                "                    trunc(lafi.dt_rep) = to_date(?DATA, 'DD/MM/YYYY') " +
            "                and lafi.sg_fuc_hst in ('REPVRCOMCC', " +
            "                                        'REPVRCOMCD', " +
            "                                        'REPVRCOMCI', " +
            "                                        'REPVRCOMCL', " +
            "                                        'REPVRCOMCJ', " +
            "                                        'REPVRCOMCS', " +
            "                                        'COMPREPAGO', " +
            "                                       'REPVRTEFD', " +
            "                                        'REPVRTEF', " +
            "                                         'REPVRPOS', " +
            "                                         'REPVRURA', " +
            "                                         'REPVRWEB', " +
            "                                         'REPVRTUC', " +
            "                                         'REPVRADM') " +
            "                 and lafi.cd_loj = 132910 " +
            //                "                 and lafi.cd_loj = ?INT " +
            "                 and (lafi.cd_loj in " +
            "                      (select t411loja.cd_loj " +
            "                         from autorizador.t411loja " +
            "                        where t411loja.cd_lji = 2 " +
            //                "                        where t411loja.cd_lji = ?INT " +
            "                          and t411loja.id_apu_idp = 'N') or " +
            "                     (lafi.cd_loj is null and lafi.cd_lji = 2  " +
            //                "                     (lafi.cd_loj is null and lafi.cd_lji = ?INT  " +
            "                      ))) oper " +
            "        group by oper.nr_ctr, " +
            "                 oper.ad_lib, " +
            "             oper.cd_loj, " +
            "              oper.cd_car, " +
            "               oper.nm_por, " +
            "                oper.nr_par " +
            "        order by 2, oper.nr_ctr, oper.ad_lib, oper.nr_par) operacoes " +
            "where tipo_operacao is not null ";
}
